package com.turing.post.salesmanage.impl;

import java.math.BigInteger;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import javax.ejb.Local;
import javax.ejb.Remote;
import javax.ejb.Stateless;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

import com.turing.post.salesmanage.util.RedempOrder;
import com.turing.post.salesmanage.util.RedempOrderDao;
import com.turing.post.util.Config;
import com.turing.post.util.GeneralDao;
import com.turing.post.util.PCPSApplicationException;
import com.turing.post.util.PCPSUtil;
import com.turing.post.util.PCPSWebInput;

@Stateless(mappedName = "salesmanage.impl.RedempOrderDaoImpl")
@Remote
@Local
public class RedempOrderDaoImpl implements RedempOrderDao {

	@PersistenceContext
	protected EntityManager _em;

	public RedempOrder getRedempOrderByRedempOrderId(BigInteger redempOrderId) {
		return _em.find(RedempOrder.class, redempOrderId);
	}

	@SuppressWarnings("unchecked")
	public RedempOrder getRedempOrderForUpdate(BigInteger redempOrderId) {
		Query query = _em
				.createNativeQuery(
						"select t.* from RedempOrder t where t.redempOrderId = ?1 for update",
						RedempOrder.class);
		query.setParameter(1, redempOrderId);
		List<RedempOrder> macc = query.getResultList();
		if (macc == null || macc.size() != 1)
			return null;
		return macc.get(0);
	}

	public void insert(RedempOrder redempOrder) throws PCPSApplicationException {
		GeneralDao.insert(_em, redempOrder);
	}

	public void update(RedempOrder redempOrder) throws PCPSApplicationException {
		GeneralDao.update(_em, redempOrder);
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<RedempOrder> getRedempOrderListByCondition(
			PCPSWebInput webInput, String redempOrderId,
			String saleBranchNo, String startTime, String endTime,
			String orderStatus, String btnStatus, Integer page) {
		if (webInput.getOperBranchNo() == null) {
			return null;
		}
		int maxResult = Config.PAGE_SIZE;
		int startPosition = maxResult * page;
		String sql = "select t.* from RedempOrder t where issueCompanyNo='"
				+ PCPSUtil.getIssueCompanyNo(webInput)
				+ "' and (?4 is null or redemporderseqno=?4) and (?1 is null or redempTime>=to_date(?1,'yyyy-mm-dd hh24:mi:ss')) and "
				+ "(?2 is null or redempTime<=to_date(?2,'yyyy-mm-dd hh24:mi:ss')) and "
				+ "(?3 is null or orderStatus=?3)";
		// 判断是查询全部还是选定机构
		sql += PCPSUtil.getSqlCond(webInput, saleBranchNo, "SaleBranchNo");
		// 前台传入菜单操作
		if (!PCPSUtil.isEmpty(btnStatus)) {
			sql = getSql(btnStatus, sql);
		}
		sql += " order by redempTime asc";
		Query query = null;
		if (page.intValue() == -1) {
			query = _em.createNativeQuery(sql, RedempOrder.class);
		} else {
			query = _em.createNativeQuery(sql, RedempOrder.class)
					.setFirstResult(startPosition).setMaxResults(maxResult);
		}
		// 起始时间
		if (PCPSUtil.isEmpty(startTime)) {
			query.setParameter(1, "");
		} else {
			query.setParameter(1, startTime + " 00:00:00");
		}
		if (PCPSUtil.isEmpty(endTime)) {
			query.setParameter(2, "");
		} else {
			query.setParameter(2, endTime + " 23:59:59");
		}
		query.setParameter(3, orderStatus);
		query.setParameter(4, redempOrderId);
		List<RedempOrder> orderList = query.getResultList();
		List<RedempOrder> redOrderList = new ArrayList<RedempOrder>();
		// 前台传入菜单操作
		if (!PCPSUtil.isEmpty(btnStatus)) {
			for (RedempOrder ao : orderList) {
				if (btnStatus.equals(RedempOrder.BtnStatus_RedEdit)) {
					ao.setBtnStatus(RedempOrder.BtnStatus_RedEdit);
					redOrderList.add(ao);
				} else if (btnStatus.equals(RedempOrder.BtnStatus_RedBindCard)) {
					ao.setBtnStatus(RedempOrder.BtnStatus_RedBindCard);
					redOrderList.add(ao);
				} else if (btnStatus.equals(RedempOrder.BtnStatus_RedConfirm)) {
					ao.setBtnStatus(RedempOrder.BtnStatus_RedConfirm);
					redOrderList.add(ao);
				} else if (btnStatus.equals(RedempOrder.BtnStatus_RedCancel)) {
					ao.setBtnStatus(RedempOrder.BtnStatus_RedCancel);
					redOrderList.add(ao);
				} else if (btnStatus.equals(RedempOrder.BtnStatus_RedFirPass)) {
					ao.setBtnStatus(RedempOrder.BtnStatus_RedFirPass);// 初审or拒绝
					redOrderList.add(ao);
				} else if (btnStatus.equals(RedempOrder.BtnStatus_RedSecPass)) {
					if (ao.getOrderStatus().equals(
							RedempOrder.OrderStatus_SecPass)) {
						ao.setBtnStatus(RedempOrder.BtnStatus_DelAccount);// 扣减冻结资金
//					} else if (ao.getOrderStatus().equals(
//							RedempOrder.OrderStatus_FirPass)) {
//						ao.setBtnStatus(RedempOrder.BtnStatus_RedSecPass);// 终审or拒绝
					}
					redOrderList.add(ao);
				} else if (btnStatus.equals(RedempOrder.BtnStatus_RedDebit)) {
					ao.setBtnStatus(RedempOrder.BtnStatus_RedDebit);
					redOrderList.add(ao);
				} else if (btnStatus.equals(RedempOrder.BtnStatus_RedPaid)) {
					ao.setBtnStatus(RedempOrder.BtnStatus_RedPaid);
					redOrderList.add(ao);
				}
			}
			// 循环结束返回
			return redOrderList;
		}
		return orderList;
	}

	@Override
	public BigInteger getRedempOrderTotalByCondition(PCPSWebInput webInput,
			String redempOrderId, String saleBranchNo, String startTime,
			String endTime, String orderStatus, String btnStatus) {
		String sql = "select nvl(count(*),0) from RedempOrder t where issueCompanyNo='"
				+ PCPSUtil.getIssueCompanyNo(webInput)
				+ "' and (?4 is null or redemporderseqno=?4) and (?1 is null or redempTime>=to_date(?1,'yyyy-mm-dd hh24:mi:ss')) and "
				+ "(?2 is null or redempTime<=to_date(?2,'yyyy-mm-dd hh24:mi:ss')) and "
				+ "(?3 is null or orderStatus=?3)";
		// 判断是查询全部还是选定机构
		sql += PCPSUtil.getSqlCond(webInput, saleBranchNo, "SaleBranchNo");
		// 前台传入菜单操作
		if (!PCPSUtil.isEmpty(btnStatus)) {
			sql = getSql(btnStatus, sql);
		}
		Query query = _em.createNativeQuery(sql);
		// 起始时间
		if (PCPSUtil.isEmpty(startTime)) {
			query.setParameter(1, "");
		} else {
			query.setParameter(1, startTime + " 00:00:00");
		}
		if (PCPSUtil.isEmpty(endTime)) {
			query.setParameter(2, "");
		} else {
			query.setParameter(2, endTime + " 23:59:59");
		}
		query.setParameter(3, orderStatus);
		query.setParameter(4, redempOrderId);
		return new BigInteger(query.getSingleResult().toString());
	}

	// 前台传入菜单判断状态
	private String getSql(String btnStatus, String sql) {
		if (btnStatus.equals(RedempOrder.BtnStatus_RedEdit)
				|| btnStatus.equals(RedempOrder.BtnStatus_RedBindCard)
				|| btnStatus.equals(RedempOrder.BtnStatus_RedConfirm)
				|| btnStatus.equals(RedempOrder.BtnStatus_RedCancel)) {
			sql = sql + " and t.OrderStatus ='" + RedempOrder.OrderStatus_Input
					+ "'";
		} else if (btnStatus.equals(RedempOrder.BtnStatus_RedFirPass)) {
			sql = sql + " and t.OrderStatus ='"
					+ RedempOrder.OrderStatus_Confirm + "'";
		} else if (btnStatus.equals(RedempOrder.BtnStatus_RedSecPass)) {
			sql = sql + " and t.OrderStatus = '"
					+ RedempOrder.OrderStatus_SecPass + "'";
		} else if (btnStatus.equals(RedempOrder.BtnStatus_RedDebit)) {
			sql = sql + " and t.OrderStatus ='"
					+ RedempOrder.OrderStatus_SecPass + "'";
		} else if (btnStatus.equals(RedempOrder.BtnStatus_RedPaid)) {
			sql = sql + " and t.OrderStatus ='" + RedempOrder.OrderStatus_Debit
					+ "'";
		}
		return sql;
	}

	/**
	 * 根据时间，发卡商编号，状态预警金额,查询 return List<RedempOrder>
	 */
	@SuppressWarnings("unchecked")
	public List<Object[]> getRedempOrderByAlarm(String issuecompanyno,
			Date starttranDate, Date endtranDate, Double rulemoney)
			throws PCPSApplicationException {
		if (PCPSUtil.isEmpty(issuecompanyno) || starttranDate == null
				|| endtranDate == null)
			return null;

		String sql = "select sum(TotalFactRedempAmount),PersonalBuyerId,OrganizeBuyerId   from RedempOrder where"
				+ " TranDate >= ?2 and TranDate<=?3  and IssueCompanyNo = ?1"
				+ " and OrderStatus in ('4', '5')  and (PersonalBuyerId is not null or OrganizeBuyerId is not null)  group by PersonalBuyerId,OrganizeBuyerId having sum(TotalFactRedempAmount) >=?4";
		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, issuecompanyno);
		query.setParameter(2, starttranDate);
		query.setParameter(3, endtranDate);
		query.setParameter(4, rulemoney);
		List<Object[]> list = query.getResultList();
		return list;
	}

	/**
	 * 根据时间，发卡商编号，状态,赎回人id查询 return List<RedempOrder>
	 */
	@SuppressWarnings("unchecked")
	public List<RedempOrder> getRedempOrder(String issuecompanyno,
			Date starttranDate, Date endtranDate, String onwerid)
			throws PCPSApplicationException {
		if (PCPSUtil.isEmpty(issuecompanyno) || starttranDate == null
				|| endtranDate == null || PCPSUtil.isEmpty(onwerid))
			return null;
		String sql = "select *   from RedempOrder where"
				+ " TranDate >= ?2 and TranDate<=?3  and IssueCompanyNo = ?1"
				+ " and OrderStatus in ('4', '5')  and (PersonalBuyerId=?4 or OrganizeBuyerId=?4) order by TranDate desc";
		Query query = _em.createNativeQuery(sql, RedempOrder.class);
		query.setParameter(1, issuecompanyno);
		query.setParameter(2, starttranDate);
		query.setParameter(3, endtranDate);
		query.setParameter(4, onwerid);
		return query.getResultList();
	}

	/**
	 * 统计指定机构的赎回业务
	 * 
	 * @param saleBranchNo
	 * @param tranDate
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public BigInteger[] getBranchDaySettByBranchAndTranDate(
			String saleBranchNo, Date tranDate) {
		/* 0 - 业务笔数 1 - 赎回金额 */
		BigInteger[] bis = new BigInteger[2];
		bis[0] = bis[1] = BigInteger.ZERO;

		String sql = "select count(distinct RedempOrderId), sum(TotalRedempAmount) from RedempOrder where SaleBranchNo = ?1 AND TranDate = ?2";
		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, saleBranchNo);
		query.setParameter(2, tranDate);
		List<Object[]> objlist = query.getResultList();
		if (objlist == null || objlist.size() != 1)
			return bis;
		Object[] obj = objlist.get(0);
		if (obj[0] != null)
			bis[0] = new BigInteger(obj[0].toString());
		if (obj[1] != null)
			bis[1] = new BigInteger(obj[1].toString());
		return bis;
	}

	@Override
	public Object[] getRedempOrderListByConditionSum(PCPSWebInput webInput,
			String redempOrderId, String saleBranchNo, String startTime,
			String endTime, String orderStatus, String btnStatus) {
		if (webInput.getOperBranchNo() == null) {
			return null;
		}
		String sql = "select sum(TotalRedempAmount),sum(TotalFee),sum(TotalFactRedempAmount) from RedempOrder t where issueCompanyNo='"
				+ PCPSUtil.getIssueCompanyNo(webInput)
				+ "' and (?4 is null or redemporderseqno=?4) and (?1 is null or redempTime>=to_date(?1,'yyyy-mm-dd hh24:mi:ss')) and "
				+ "(?2 is null or redempTime<=to_date(?2,'yyyy-mm-dd hh24:mi:ss')) and "
				+ "(?3 is null or orderStatus=?3) ";
		if (!RedempOrder.OrderStatus_CanRefuse.equals(orderStatus)) {
			sql = sql + " and t.orderStatus<>'" + RedempOrder.OrderStatus_CanRefuse + "' ";
		}
		// 判断是查询全部还是选定机构
		sql += PCPSUtil.getSqlCond(webInput, saleBranchNo, "SaleBranchNo");
		// 前台传入菜单操作
		if (!PCPSUtil.isEmpty(btnStatus)) {
			sql = getSql(btnStatus, sql);
		}
		sql += " order by redempTime asc";
		Query query = _em.createNativeQuery(sql);
		// 起始时间
		if (PCPSUtil.isEmpty(startTime)) {
			query.setParameter(1, "");
		} else {
			query.setParameter(1, startTime + " 00:00:00");
		}
		if (PCPSUtil.isEmpty(endTime)) {
			query.setParameter(2, "");
		} else {
			query.setParameter(2, endTime + " 23:59:59");
		}
		query.setParameter(3, orderStatus);
		query.setParameter(4, redempOrderId);
		Object[] obj = (Object[])query.getSingleResult();
		
		return obj;
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public RedempOrder getRedempOrderByredempOrderSeqNo(String redempOrderSeqNo) {
		String sql = "select * from RedempOrder t where t.redempOrderSeqNo= ?1";
		Query query = _em.createNativeQuery(sql, RedempOrder.class);
		query.setParameter(1, redempOrderSeqNo);
		List<RedempOrder> list = query.getResultList();
		if (list == null || list.size() < 1) {
			return null;
		}
		return list.get(0);
	}
}